package com.example.project.service.impl;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.example.project.service.ProjectReportService;
import com.example.project.vo.ProjectReportRequest;

/**
 * 项目报表服务实现
 * 
 * @author ruoyi
 * @date 2025-10-24
 */
@Service
public class ProjectReportServiceImpl implements ProjectReportService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static final SimpleDateFormat FILE_DATE_FORMAT = new SimpleDateFormat("yyyyMMdd_HHmmss");

    @Override
    public Workbook generateExcelReport(ProjectReportRequest request) {
        // 创建Excel工作簿（使用SXSSFWorkbook支持大数据量）
        Workbook workbook = new SXSSFWorkbook(100); // 在内存中保留100行，其余写入磁盘
        
        try {
            // 根据报表类型生成不同的sheet
            switch (request.getReportType()) {
                case PROJECT_BASIC_INFO:
                    generateBasicInfoSheet(workbook, request);
                    break;
                case PROJECT_STATISTICS:
                    generateStatisticsSheet(workbook, request);
                    break;
                case ISSUE_DETAIL_REPORT:
                    generateIssueDetailSheet(workbook, request);
                    break;
                case CUSTOMER_PROJECT_REPORT:
                    generateCustomerProjectSheet(workbook, request);
                    break;
                case COMPREHENSIVE_REPORT:
                    generateComprehensiveSheet(workbook, request);
                    break;
                default:
                    generateBasicInfoSheet(workbook, request);
                    break;
            }
            
            return workbook;
            
        } catch (Exception e) {
            throw new RuntimeException("生成Excel报表失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 生成项目基本信息Sheet
     */
    private void generateBasicInfoSheet(Workbook workbook, ProjectReportRequest request) {
        Sheet sheet = workbook.createSheet("项目基本信息");
        
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("项目基本信息报表");
        
        // 设置标题样式
        CellStyle titleStyle = createTitleStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        
        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"项目ID", "项目名称", "客户名称", "项目状态", "签约日期", "到期日期", "开发语言", "创建时间"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 查询数据
        List<Map<String, Object>> data = getProjectBasicInfoData(request);
        
        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        int rowNum = 3;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            
            int colNum = 0;
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目ID")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("客户名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目状态")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("签约日期")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("到期日期")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("开发语言")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("创建时间")));
            
            // 设置数据行样式
            for (int i = 0; i < headers.length; i++) {
                dataRow.getCell(i).setCellStyle(dataStyle);
            }
        }
        
        // 设置行高
        titleRow.setHeightInPoints(30); // 标题行高度
        headerRow.setHeightInPoints(25); // 表头行高度
        
        // 设置列宽（比自动调整更宽一些）
        for (int i = 0; i < headers.length; i++) {
            // 根据列内容设置不同的宽度
            int columnWidth = 4000; // 默认宽度
            
            // 根据列标题调整宽度
            String header = headers[i];
            if (header.contains("名称") || header.contains("主题")) {
                columnWidth = 8000; // 名称类列更宽
            } else if (header.contains("日期") || header.contains("时间")) {
                columnWidth = 5000; // 日期类列中等宽度
            } else if (header.contains("ID")) {
                columnWidth = 3000; // ID类列较窄
            }
            
            sheet.setColumnWidth(i, columnWidth);
        }
        
        // 设置数据行高度
        for (int i = 3; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                row.setHeightInPoints(20); // 数据行高度
            }
        }
    }
    
    /**
     * 生成项目统计Sheet
     */
    private void generateStatisticsSheet(Workbook workbook, ProjectReportRequest request) {
        Sheet sheet = workbook.createSheet("项目统计");
        
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("项目统计报表");
        
        CellStyle titleStyle = createTitleStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
        
        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"项目ID", "项目名称", "客户名称", "项目状态", "总需求数", "已关闭需求", "未关闭需求", "需求完成率(%)", "平均处理时效(小时)"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 查询数据
        List<Map<String, Object>> data = getProjectStatisticsData(request);
        
        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        int rowNum = 3;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            
            int colNum = 0;
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目ID")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("客户名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目状态")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("总需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("已关闭需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("未关闭需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("需求完成率(%)")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("平均处理时效(小时)")));
            
            // 设置数据行样式
            for (int i = 0; i < headers.length; i++) {
                dataRow.getCell(i).setCellStyle(dataStyle);
            }
        }
        
        // 设置行高
        titleRow.setHeightInPoints(30); // 标题行高度
        headerRow.setHeightInPoints(25); // 表头行高度
        
        // 设置列宽（比自动调整更宽一些）
        for (int i = 0; i < headers.length; i++) {
            // 根据列内容设置不同的宽度
            int columnWidth = 4000; // 默认宽度
            
            // 根据列标题调整宽度
            String header = headers[i];
            if (header.contains("名称") || header.contains("主题")) {
                columnWidth = 8000; // 名称类列更宽
            } else if (header.contains("日期") || header.contains("时间")) {
                columnWidth = 5000; // 日期类列中等宽度
            } else if (header.contains("ID")) {
                columnWidth = 3000; // ID类列较窄
            }
            
            sheet.setColumnWidth(i, columnWidth);
        }
        
        // 设置数据行高度
        for (int i = 3; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                row.setHeightInPoints(20); // 数据行高度
            }
        }
    }
    
    /**
     * 生成问题明细Sheet
     */
    private void generateIssueDetailSheet(Workbook workbook, ProjectReportRequest request) {
        Sheet sheet = workbook.createSheet("问题明细");
        
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("问题明细报表");
        
        CellStyle titleStyle = createTitleStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        
        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"项目名称", "问题ID", "问题主题", "问题类型", "当前状态", "是否关闭", "问题分类", "创建人", "指派给", "开始日期", "截止日期", "完成进度(%)"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 查询数据
        List<Map<String, Object>> data = getIssueDetailData(request);
        
        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        int rowNum = 3;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            
            int colNum = 0;
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("问题ID")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("问题主题")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("问题类型")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("当前状态")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("是否关闭")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("问题分类")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("创建人")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("指派给")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("开始日期")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("截止日期")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("完成进度(%)")));
            
            // 设置数据行样式
            for (int i = 0; i < headers.length; i++) {
                dataRow.getCell(i).setCellStyle(dataStyle);
            }
        }
        
        // 设置行高
        titleRow.setHeightInPoints(30); // 标题行高度
        headerRow.setHeightInPoints(25); // 表头行高度
        
        // 设置列宽（比自动调整更宽一些）
        for (int i = 0; i < headers.length; i++) {
            // 根据列内容设置不同的宽度
            int columnWidth = 4000; // 默认宽度
            
            // 根据列标题调整宽度
            String header = headers[i];
            if (header.contains("名称") || header.contains("主题")) {
                columnWidth = 8000; // 名称类列更宽
            } else if (header.contains("日期") || header.contains("时间")) {
                columnWidth = 5000; // 日期类列中等宽度
            } else if (header.contains("ID")) {
                columnWidth = 3000; // ID类列较窄
            }
            
            sheet.setColumnWidth(i, columnWidth);
        }
        
        // 设置数据行高度
        for (int i = 3; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                row.setHeightInPoints(20); // 数据行高度
            }
        }
    }
    
    /**
     * 生成客户项目关系Sheet
     */
    private void generateCustomerProjectSheet(Workbook workbook, ProjectReportRequest request) {
        Sheet sheet = workbook.createSheet("客户项目关系");
        
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("客户项目关系报表");
        
        CellStyle titleStyle = createTitleStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        
        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"公司名称", "公司网址", "所属商家", "合作项目数", "总需求数", "已完成需求", "进行中需求", "最早签约日期", "最晚到期日期"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 查询数据
        List<Map<String, Object>> data = getCustomerProjectData(request);
        
        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        int rowNum = 3;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            
            int colNum = 0;
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("公司名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("公司网址")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("所属商家")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("合作项目数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("总需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("已完成需求")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("进行中需求")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("最早签约日期")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("最晚到期日期")));
            
            // 设置数据行样式
            for (int i = 0; i < headers.length; i++) {
                dataRow.getCell(i).setCellStyle(dataStyle);
            }
        }
        
        // 设置行高
        titleRow.setHeightInPoints(30); // 标题行高度
        headerRow.setHeightInPoints(25); // 表头行高度
        
        // 设置列宽（比自动调整更宽一些）
        for (int i = 0; i < headers.length; i++) {
            // 根据列内容设置不同的宽度
            int columnWidth = 4000; // 默认宽度
            
            // 根据列标题调整宽度
            String header = headers[i];
            if (header.contains("名称") || header.contains("主题")) {
                columnWidth = 8000; // 名称类列更宽
            } else if (header.contains("日期") || header.contains("时间")) {
                columnWidth = 5000; // 日期类列中等宽度
            } else if (header.contains("ID")) {
                columnWidth = 3000; // ID类列较窄
            }
            
            sheet.setColumnWidth(i, columnWidth);
        }
        
        // 设置数据行高度
        for (int i = 3; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                row.setHeightInPoints(20); // 数据行高度
            }
        }
    }
    
    /**
     * 生成综合项目分析Sheet
     */
    private void generateComprehensiveSheet(Workbook workbook, ProjectReportRequest request) {
        Sheet sheet = workbook.createSheet("综合项目分析");
        
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("综合项目分析报表");
        
        CellStyle titleStyle = createTitleStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        
        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"项目ID", "项目名称", "客户名称", "项目状态", "签约日期", "到期日期", "总需求数", "日均需求数", "平均处理天数", "需求完成率(%)", "参与人员数", "时间风险等级", "进度评估"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 查询数据
        List<Map<String, Object>> data = getComprehensiveAnalysisData(request);
        
        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        int rowNum = 3;
        for (Map<String, Object> row : data) {
            Row dataRow = sheet.createRow(rowNum++);
            
            int colNum = 0;
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目ID")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("客户名称")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("项目状态")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("签约日期")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("到期日期")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("总需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("日均需求数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("平均处理天数")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("需求完成率(%)")));
            dataRow.createCell(colNum++).setCellValue(getNumericValue(row.get("参与人员数")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("时间风险等级")));
            dataRow.createCell(colNum++).setCellValue(getStringValue(row.get("进度评估")));
            
            // 设置数据行样式
            for (int i = 0; i < headers.length; i++) {
                dataRow.getCell(i).setCellStyle(dataStyle);
            }
        }
        
        // 设置行高
        titleRow.setHeightInPoints(30); // 标题行高度
        headerRow.setHeightInPoints(25); // 表头行高度
        
        // 设置列宽（比自动调整更宽一些）
        for (int i = 0; i < headers.length; i++) {
            // 根据列内容设置不同的宽度
            int columnWidth = 4000; // 默认宽度
            
            // 根据列标题调整宽度
            String header = headers[i];
            if (header.contains("名称") || header.contains("主题")) {
                columnWidth = 8000; // 名称类列更宽
            } else if (header.contains("日期") || header.contains("时间")) {
                columnWidth = 5000; // 日期类列中等宽度
            } else if (header.contains("ID")) {
                columnWidth = 3000; // ID类列较窄
            }
            
            sheet.setColumnWidth(i, columnWidth);
        }
        
        // 设置数据行高度
        for (int i = 3; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                row.setHeightInPoints(20); // 数据行高度
            }
        }
    }
    
    // 样式创建方法
    private CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short)16);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }
    
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    
    // 数据转换方法
    private String getStringValue(Object value) {
        if (value == null) return "";
        return value.toString();
    }
    
    private double getNumericValue(Object value) {
        if (value == null) return 0.0;
        try {
            return Double.parseDouble(value.toString());
        } catch (NumberFormatException e) {
            return 0.0;
        }
    }
    
    // 数据查询方法实现
    @Override
    public List<Map<String, Object>> getProjectBasicInfoData(ProjectReportRequest request) {
        String sql = "SELECT " +
                "p.id AS '项目ID', " +
                "p.name AS '项目名称', " +
                "p.customer_name AS '客户名称', " +
                "CASE p.state WHEN 1 THEN '进行中' WHEN 2 THEN '已完成' WHEN 3 THEN '暂停' WHEN 4 THEN '已取消' ELSE '未知' END AS '项目状态', " +
                "p.sign_date AS '签约日期', " +
                "p.expir_date AS '到期日期', " +
                "p.develop_language AS '开发语言', " +
                "p.create_time AS '创建时间' " +
                "FROM t_project p " +
                "WHERE p.del_flag = 0 " +
                "AND p.create_time BETWEEN ? AND ? " +
                "ORDER BY p.create_time DESC";
        
        return jdbcTemplate.queryForList(sql, request.getStartDate(), request.getEndDate());
    }
    
    @Override
    public List<Map<String, Object>> getProjectStatisticsData(ProjectReportRequest request) {
        String sql = "SELECT " +
                "p.id AS '项目ID', " +
                "p.name AS '项目名称', " +
                "p.customer_name AS '客户名称', " +
                "CASE p.state WHEN 1 THEN '进行中' WHEN 2 THEN '已完成' WHEN 3 THEN '暂停' WHEN 4 THEN '已取消' ELSE '未知' END AS '项目状态', " +
                "COUNT(DISTINCT i.id) AS '总需求数', " +
                "SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) AS '已关闭需求数', " +
                "SUM(CASE WHEN i.status_id NOT IN (4, 6) THEN 1 ELSE 0 END) AS '未关闭需求数', " +
                "ROUND(SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT i.id), 0), 2) AS '需求完成率(%)', " +
                "ROUND(AVG(CASE WHEN i.status_id IN (4, 6) THEN TIMESTAMPDIFF(HOUR, i.create_time, i.update_time) ELSE NULL END), 2) AS '平均处理时效(小时)' " +
                "FROM t_project p " +
                "LEFT JOIN t_issue i ON p.id = i.project_id AND i.del_flag = 0 " +
                "WHERE p.del_flag = 0 " +
                "AND i.create_time BETWEEN ? AND ? " +
                "GROUP BY p.id, p.name, p.customer_name, p.state " ;
        
        return jdbcTemplate.queryForList(sql, request.getStartDate(), request.getEndDate());
    }
    
    @Override
    public List<Map<String, Object>> getIssueDetailData(ProjectReportRequest request) {
        String sql = "SELECT " +
                "p.name AS '项目名称', " +
                "i.id AS '问题ID', " +
                "i.subject AS '问题主题', " +
                "t.name AS '问题类型', " +
                "iss.name AS '当前状态', " +
                "CASE WHEN i.status_id IN (4, 6) THEN '是' ELSE '否' END AS '是否关闭', " +
                "ic.name AS '问题分类', " +
                "u1.user_name AS '创建人', " +
                "u2.user_name AS '指派给', " +
                "i.start_date AS '开始日期', " +
                "i.due_date AS '截止日期', " +
                "i.done_ratio AS '完成进度(%)' " +
                "FROM t_issue i " +
                "INNER JOIN t_project p ON i.project_id = p.id AND p.del_flag = 0 " +
                "LEFT JOIN t_tracker t ON i.tracker_id = t.id " +
                "LEFT JOIN t_issue_status iss ON i.status_id = iss.id " +
                "LEFT JOIN t_issue_categorie ic ON i.category_id = ic.id " +
                "LEFT JOIN t_user u1 ON i.author_id = u1.user_id " +
                "LEFT JOIN t_user u2 ON i.assigned_to_id = u2.user_id " +
                "WHERE i.del_flag = 0 " +
                "AND i.create_time BETWEEN ? AND ? " +
                "ORDER BY p.name, i.create_time DESC";
        
        return jdbcTemplate.queryForList(sql, request.getStartDate(), request.getEndDate());
    }
    
    @Override
    public List<Map<String, Object>> getCustomerProjectData(ProjectReportRequest request) {
        String sql = "SELECT " +
                "c.company_name AS '公司名称', " +
                "c.company_url AS '公司网址', " +
                "c.merchant_name AS '所属商家', " +
                "COUNT(DISTINCT p.id) AS '合作项目数', " +
                "COUNT(DISTINCT i.id) AS '总需求数', " +
                "SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) AS '已完成需求', " +
                "SUM(CASE WHEN i.status_id NOT IN (4, 6) THEN 1 ELSE 0 END) AS '进行中需求', " +
                "MIN(p.sign_date) AS '最早签约日期', " +
                "MAX(p.expir_date) AS '最晚到期日期' " +
                "FROM t_company c " +
                "INNER JOIN t_project p ON c.project_id = p.id AND p.del_flag = 0 " +
                "LEFT JOIN t_issue i ON p.id = i.project_id AND i.del_flag = 0 " +
                "WHERE c.del_flag = 0 " +
                "AND i.create_time BETWEEN ? AND ? " +
                "GROUP BY c.id, c.company_name, c.company_url, c.merchant_name " +
                "ORDER BY COUNT(DISTINCT p.id) DESC, c.company_name";
        
        return jdbcTemplate.queryForList(sql, request.getStartDate(), request.getEndDate());
    }
    
    @Override
    public List<Map<String, Object>> getComprehensiveAnalysisData(ProjectReportRequest request) {
        String sql = "SELECT " +
                "p.id AS '项目ID', " +
                "p.name AS '项目名称', " +
                "p.customer_name AS '客户名称', " +
                "CASE p.state WHEN 1 THEN '进行中' WHEN 2 THEN '已完成' WHEN 3 THEN '暂停' WHEN 4 THEN '已取消' ELSE '未知' END AS '项目状态', " +
                "p.sign_date AS '签约日期', " +
                "p.expir_date AS '到期日期', " +
                "COUNT(DISTINCT i.id) AS '总需求数', " +
                "ROUND(COUNT(DISTINCT i.id) / NULLIF(DATEDIFF(p.expir_date, p.sign_date), 0), 2) AS '日均需求数', " +
                "ROUND(AVG(CASE WHEN i.status_id IN (4, 6) THEN TIMESTAMPDIFF(DAY, i.create_time, i.update_time) ELSE NULL END), 2) AS '平均处理天数', " +
                "ROUND(SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT i.id), 0), 2) AS '需求完成率(%)', " +
                "COUNT(DISTINCT i.assigned_to_id) AS '参与人员数', " +
                "CASE WHEN DATEDIFF(p.expir_date, CURDATE()) < 30 THEN '高风险' WHEN DATEDIFF(p.expir_date, CURDATE()) < 90 THEN '中风险' ELSE '低风险' END AS '时间风险等级', " +
                "CASE WHEN COUNT(DISTINCT i.id) = 0 THEN '无需求' WHEN SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT i.id) > 80 THEN '进展良好' WHEN SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT i.id) > 50 THEN '正常进展' ELSE '进展缓慢' END AS '进度评估' " +
                "FROM t_project p " +
                "LEFT JOIN t_issue i ON p.id = i.project_id AND i.del_flag = 0 " +
                "WHERE p.del_flag = 0 " +
                "AND i.create_time BETWEEN ? AND ? " +
                "GROUP BY p.id, p.name, p.customer_name, p.state, p.sign_date, p.expir_date " +
                "ORDER BY p.create_time DESC";
        
        return jdbcTemplate.queryForList(sql, request.getStartDate(), request.getEndDate());
    }
}